﻿Imports System.Windows.Forms
Imports Microsoft.Office.Interop
Imports System.Data.DataTable
Imports System.Data
Public Class Import
    

   Public Shared Function Get_Nha_FromExcel(ByVal path As String) As DataTable

        Dim app As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
        Dim workBook As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Open(path, 0, True, 5,
            String.Empty, String.Empty, True, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
            String.Empty, True, False, 0, True, True, False)

        Dim workSheet As Microsoft.Office.Interop.Excel.Worksheet = workBook.Worksheets(1)
        Dim dt As DataTable = New DataTable()
        If workSheet.Name = "Danh sach nha" Then
            Dim rowIndex As Integer = 4

            dt.Columns.Add("MaNha")
            dt.Columns.Add("SoNha")
            dt.Columns.Add("Duong")
            dt.Columns.Add("DienTich")
            dt.Columns.Add("Gia")
            dt.Columns.Add("TinhTrangNha")
            dt.Columns.Add("MaLoaiNha")
            dt.Columns.Add("MaQuan")
            dt.Columns.Add("TamXoa")
            dt.Columns.Add("MaNguoiDang")
            dt.Columns.Add("TieuDe")
            dt.Columns.Add("NgayDang")

            Dim row As DataRow
            While Not IsNothing(CType(workSheet.Cells(rowIndex, 1), Microsoft.Office.Interop.Excel.Range).Value2)
                row = dt.NewRow()
                row(0) = Convert.ToString((CType(workSheet.Cells(rowIndex, 1), Microsoft.Office.Interop.Excel.Range).Value2))
                row(1) = Convert.ToString((CType(workSheet.Cells(rowIndex, 2), Microsoft.Office.Interop.Excel.Range).Value2))
                row(2) = Convert.ToString((CType(workSheet.Cells(rowIndex, 3), Microsoft.Office.Interop.Excel.Range).Value2))
                row(3) = Convert.ToString((CType(workSheet.Cells(rowIndex, 4), Microsoft.Office.Interop.Excel.Range).Value2))
                row(4) = Convert.ToString((CType(workSheet.Cells(rowIndex, 5), Microsoft.Office.Interop.Excel.Range).Value2))
                row(5) = Convert.ToString((CType(workSheet.Cells(rowIndex, 6), Microsoft.Office.Interop.Excel.Range).Value2))
                row(6) = Convert.ToString((CType(workSheet.Cells(rowIndex, 7), Microsoft.Office.Interop.Excel.Range).Value2))
                row(7) = Convert.ToString((CType(workSheet.Cells(rowIndex, 8), Microsoft.Office.Interop.Excel.Range).Value2))
                row(8) = Convert.ToString((CType(workSheet.Cells(rowIndex, 9), Microsoft.Office.Interop.Excel.Range).Value2))
                row(9) = Convert.ToString((CType(workSheet.Cells(rowIndex, 10), Microsoft.Office.Interop.Excel.Range).Value2))
                row(10) = Convert.ToString((CType(workSheet.Cells(rowIndex, 11), Microsoft.Office.Interop.Excel.Range).Value2))
                row(11) = Convert.ToString((CType(workSheet.Cells(rowIndex, 12), Microsoft.Office.Interop.Excel.Range).Text))
                rowIndex = rowIndex + 1
                dt.Rows.Add(row)
            End While
            app.Workbooks.Close()
        Else
            MessageBox.Show("Bạn đã chọn một file KHÔNG có nội dung danh sách Nhà !!", "THÔNG BÁO")


        End If
        Return dt


    End Function


    Public Shared Function Get_KhachHang_FromExcel(ByVal path As String)

        Dim app As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
        Dim workBook As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Open(path, 0, True, 5,
            String.Empty, String.Empty, True, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
            String.Empty, True, False, 0, True, True, False)

        Dim workSheet As Microsoft.Office.Interop.Excel.Worksheet = workBook.Worksheets(1)
        Dim dt As DataTable = New DataTable()
        If workSheet.Name = "Danh sach khach hang" Then
            Dim rowIndex As Integer = 4

            dt.Columns.Add("MaKhachThue")
            dt.Columns.Add("HoTen")
            dt.Columns.Add("DienThoai")
            dt.Columns.Add("TinhTrang")
            dt.Columns.Add("TamXoa")

            Dim row As DataRow
            While Not IsNothing(CType(workSheet.Cells(rowIndex, 1), Microsoft.Office.Interop.Excel.Range).Value2)
                row = dt.NewRow()
                row(0) = Convert.ToString((CType(workSheet.Cells(rowIndex, 1), Microsoft.Office.Interop.Excel.Range).Value2))
                row(1) = Convert.ToString((CType(workSheet.Cells(rowIndex, 2), Microsoft.Office.Interop.Excel.Range).Value2))
                row(2) = Convert.ToString((CType(workSheet.Cells(rowIndex, 3), Microsoft.Office.Interop.Excel.Range).Value2))
                row(3) = Convert.ToString((CType(workSheet.Cells(rowIndex, 4), Microsoft.Office.Interop.Excel.Range).Value2))
                row(4) = Convert.ToString((CType(workSheet.Cells(rowIndex, 5), Microsoft.Office.Interop.Excel.Range).Value2))
                rowIndex = rowIndex + 1
                dt.Rows.Add(row)
            End While
            app.Workbooks.Close()
        Else
            MessageBox.Show("Bạn đã chọn một file KHÔNG có nội dung danh sách Khách Hàng !!", "THÔNG BÁO")


        End If
        Return dt


    End Function



    Public Shared Function Get_ChuNha_FromExcel(ByVal path As String)

        Dim app As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
        Dim workBook As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Open(path, 0, True, 5,
            String.Empty, String.Empty, True, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
            String.Empty, True, False, 0, True, True, False)

        Dim workSheet As Microsoft.Office.Interop.Excel.Worksheet = workBook.Worksheets(1)
        Dim dt As DataTable = New DataTable()
        If workSheet.Name = "Danh sach chu nha" Then
            Dim rowIndex As Integer = 4

            dt.Columns.Add("MaNguoiDang")
            dt.Columns.Add("HoTen")
            dt.Columns.Add("DiaChi")
            dt.Columns.Add("DienThoai")
            dt.Columns.Add("Email")
            dt.Columns.Add("TamXoa")

            Dim row As DataRow
            While Not IsNothing(CType(workSheet.Cells(rowIndex, 1), Microsoft.Office.Interop.Excel.Range).Value2)
                row = dt.NewRow()
                row(0) = Convert.ToString((CType(workSheet.Cells(rowIndex, 1), Microsoft.Office.Interop.Excel.Range).Value2))
                row(1) = Convert.ToString((CType(workSheet.Cells(rowIndex, 2), Microsoft.Office.Interop.Excel.Range).Value2))
                row(2) = Convert.ToString((CType(workSheet.Cells(rowIndex, 3), Microsoft.Office.Interop.Excel.Range).Value2))
                row(3) = Convert.ToString((CType(workSheet.Cells(rowIndex, 4), Microsoft.Office.Interop.Excel.Range).Value2))
                row(4) = Convert.ToString((CType(workSheet.Cells(rowIndex, 5), Microsoft.Office.Interop.Excel.Range).Value2))
                row(5) = Convert.ToString((CType(workSheet.Cells(rowIndex, 6), Microsoft.Office.Interop.Excel.Range).Value2))
                rowIndex = rowIndex + 1
                dt.Rows.Add(row)
            End While
            app.Workbooks.Close()
        Else
            MessageBox.Show("Bạn đã chọn một file KHÔNG có nội dung danh sách Chủ Nhà !!", "THÔNG BÁO")


        End If
        Return dt


    End Function
End Class
